package fr.enseirb.mycub.db;

import java.util.ArrayList;
import java.util.HashSet;

import org.json.JSONException;
import android.app.SearchManager;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.DatabaseUtils.InsertHelper;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;
import android.util.Log;

import fr.enseirb.mycub.db.structure.bike.BikeStationDAO;

public class BikeDBHelper {

	public static void insertBikeStations(SQLiteDatabase mDb, ArrayList<BikeStationDAO> jsonBikes) throws JSONException,
	SQLiteException {

		InsertHelper stationsInsertHelper = new InsertHelper(mDb,
				BikeDBConstants.BIKE_STATION_TABLE);

		final int idColumn = stationsInsertHelper
				.getColumnIndex(BaseColumns._ID);
		final int nameColumn = stationsInsertHelper.getColumnIndex(BikeDBConstants.KEY_NAME);
		final int openColumn = stationsInsertHelper.getColumnIndex(BikeDBConstants.KEY_OPEN);
		final int bikesColumn = stationsInsertHelper.getColumnIndex(BikeDBConstants.KEY_BIKES);
		final int slotsColumn = stationsInsertHelper.getColumnIndex(BikeDBConstants.KEY_SLOTS);
		final int addressColumn = stationsInsertHelper
				.getColumnIndex(BikeDBConstants.KEY_ADDRESS);
		final int latitudeColumn = stationsInsertHelper
				.getColumnIndex(BikeDBConstants.KEY_LATITUDE);
		final int longitudeColumn = stationsInsertHelper
				.getColumnIndex(BikeDBConstants.KEY_LONGITUDE);
		final int paymentColumn = stationsInsertHelper
				.getColumnIndex(BikeDBConstants.KEY_PAYMENT);
		final int specialColumn = stationsInsertHelper
				.getColumnIndex(BikeDBConstants.KEY_SPECIAL);

		final int favoriteColumn = stationsInsertHelper
				.getColumnIndex(BikeDBConstants.KEY_FAVORITE);

		final int size = jsonBikes.size();

		final String sql = "INSERT INTO " + BikeDBConstants.BIKE_STATION_TABLE
				+ " VALUES (?,?,?,?,?,?,?,?,?,?,?);";

		stationsInsertHelper.close();
		Log.i("Insert Station", "init des colonnes...");
		try {
			mDb.beginTransaction();
			SQLiteStatement insert = mDb.compileStatement(sql);
			int id;
			String name;
			Log.i("Insert Station", "debut de la boucle...");
			for (int i = 0; i < size; i++) {
				BikeStationDAO jsonStation = jsonBikes.get(i);
				id = jsonStation.getId();
				name = jsonStation.getName();
				insert.bindLong(idColumn, id);
				insert.bindString(nameColumn, name);
				insert.bindLong(openColumn, jsonStation
						.isOpen() ? 1 : 0);
				insert.bindLong(bikesColumn, jsonStation.getmBikes());
				insert.bindLong(slotsColumn, jsonStation.getmSlots());
				insert.bindString(addressColumn, jsonStation
						.getmAddress());
				insert.bindLong(latitudeColumn, (int) (jsonStation
						.getmLatitude() * 1E6));
				insert.bindLong(longitudeColumn, (int) (jsonStation
						.getmLongitude() * 1E6));
				insert.bindLong(paymentColumn, jsonStation
						.ismHasPayment() ? 1 : 0);
				insert.bindLong(specialColumn, jsonStation.ismIsSpecial() ? 1 : 0);
				insert.bindLong(favoriteColumn, 0); // Favorite
				insert.executeInsert();

			}
			mDb.setTransactionSuccessful();
		}catch (SQLException e) {
			throw e;
		} finally {
			mDb.endTransaction();
		}
	}

	public static boolean syncBikeStations(SQLiteDatabase mDb, ArrayList<BikeStationDAO> jsonBikes) throws SQLiteException,
	JSONException {
		boolean needUpdate = false;
		final int size = jsonBikes.size();
		BikeStationDAO jsonStation;
		try {
			mDb.beginTransaction();
			ContentValues contentValues = new ContentValues();
			HashSet<Integer> ids = new HashSet<Integer>(size);
			for (int i = 0; i < size; i++) {
				jsonStation = jsonBikes.get(i);
				int id = jsonStation.getId();
				ids.add(id);
				contentValues.put(BikeDBConstants.KEY_BIKES, jsonStation
						.getmBikes());
				contentValues.put(BikeDBConstants.KEY_SLOTS, jsonStation
						.getmSlots());
				contentValues.put(BikeDBConstants.KEY_OPEN, jsonStation
						.isOpen());
				if (mDb.update(BikeDBConstants.BIKE_STATION_TABLE, contentValues, BaseColumns._ID
						+ " = " + id, null) == 0) {
					needUpdate = true;
					break;
				}
			}
			if (!needUpdate) {
				Cursor cursorIds = mDb.query(BikeDBConstants.BIKE_STATION_TABLE,
						new String[] { BaseColumns._ID }, null, null, null, null, null);
				HashSet<Integer> oldIds = new HashSet<Integer>(cursorIds
						.getCount());
				if (cursorIds.moveToFirst()) {
					do {
						oldIds.add(cursorIds.getInt(0));
					} while (cursorIds.moveToNext());
				}
				oldIds.removeAll(ids);
				for (Integer id : oldIds) {
					mDb.delete(BikeDBConstants.BIKE_STATION_TABLE, BaseColumns._ID + " = " + id
							, null);
				}
			}
		} catch (SQLiteException e) {
			e.printStackTrace();
			mDb.endTransaction();
			throw e;
		}
		mDb.setTransactionSuccessful();
		mDb.endTransaction();
		return needUpdate;
	}

	public static void cleanAndInsertBikeStations(SQLiteDatabase mDb, ArrayList<BikeStationDAO> jsonBikes)
			throws JSONException {
		//Log.i("DBadapter", "cleanAndInsert");
		Cursor favorites = getFilteredStationsCursor(
				mDb, new String[] { BaseColumns._ID }, BikeDBConstants.KEY_FAVORITE + " = 1", null);
		// Get count : hack for forcing cursor query
		favorites.getCount();
		mDb.delete(BikeDBConstants.BIKE_STATION_TABLE, null, null);
		insertBikeStations(mDb,jsonBikes);
		while (favorites.moveToNext()) {
			updateFavorite(mDb,favorites.getInt(0), true);
		}
		favorites.close();
	}

	public static boolean updateFavorite(SQLiteDatabase mDb, int id, boolean isFavorite) {
		ContentValues newValues = new ContentValues();
		newValues.put(BikeDBConstants.KEY_FAVORITE, isFavorite ? 1 : 0);
		return mDb.update(BikeDBConstants.BIKE_STATION_TABLE, newValues, BaseColumns._ID
				+ " = ?", new String[] {
				String.valueOf(id) 
		}) > 0;
	}

	public static Cursor getFilteredStationsCursor(SQLiteDatabase mDb,String[] projection, String where,
			String orderBy) {
		String nWhere;
		nWhere = where;
		return mDb.query(BikeDBConstants.BIKE_STATION_TABLE, projection, nWhere,
				null, null, null,
				orderBy);
	}

	// Search results
	public static Cursor getSearchCursor(SQLiteDatabase mDb,String query) {
		String table = BikeDBConstants.BIKE_STATION_TABLE;
		try {
			Integer.parseInt(query);
			table = "vs." + BaseColumns._ID;
		} catch (NumberFormatException ex) {
		}
		query += "*";

		String s = "SELECT vs._id, ob.availableBikes, ob.freeSlots, ob.isOpen, "
				+ "ob.latitude, ob.longitude, ob.name, ob.isFavorite "
				+ "FROM bike_virtual_stations vs "
				+ "INNER JOIN "+BikeDBConstants.BIKE_STATION_TABLE+" ob "
				+ "ON (ob._id = vs._id AND vs.network = ob.network) "
				+ "WHERE "
				+ table
				+ " match ?";
		Log.i("DBsearch", s+" arg : "+query);
		Cursor cursor = mDb.rawQuery(s, new String[] { query });
		return cursor;
	}

	// Search suggestions
	public static Cursor getStationsMatches(SQLiteDatabase mDb, String query, String[] columns) {
		String table = BikeDBConstants.BIKE_STATION_TABLE;
		try {
			Integer.parseInt(query);
			table = BaseColumns._ID;
		} catch (NumberFormatException ex) {
		}
		query += "*";

		String s = "SELECT vs._id, vs._id as "
				+ SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID
				+ ", 'n ��� ' || vs._id as "
				+ SearchManager.SUGGEST_COLUMN_TEXT_2
				+ ", vs.name as "
				+ SearchManager.SUGGEST_COLUMN_TEXT_1
				+ " FROM"
				+ table + " vs WHERE "
				+ table
				+ " MATCH ? " + ";";
		Cursor cursor = mDb.rawQuery(s, new String[] { query });
		if (cursor == null) {
			return null;
		}
		return cursor;
	}

	public static Cursor getStation(SQLiteDatabase mDb, int id, String[] columns) throws SQLException {
		Cursor cursor = mDb.query(true, BikeDBConstants.BIKE_STATION_TABLE, columns,
				BaseColumns._ID + " = ?",
				new String[] {
				String.valueOf(id) },
				null, null, null, null);
		if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			throw new SQLException("No Station found with ID " + id);
		}
		return cursor;
	}

	public static int getStationCount(SQLiteDatabase mDb) throws SQLException {
		Cursor cursor = mDb.rawQuery("SELECT COUNT(*) AS count FROM "
				+ BikeDBConstants.BIKE_STATION_TABLE,null);
		cursor.moveToNext();
		int count = cursor.getInt(0);
		cursor.close();
		return count;
	}


}
